﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using Adex;
using Telerik.Web.UI;
using SubSonic.Sugar;
using System.Security;
using MediaMonitor.Common;
using Ionic.Zip;
using System.IO;
using OfficeOpenXml;
using OfficeOpenXml.Style;
using System.Drawing;
using System.Collections;
using OfficeOpenXml.Drawing.Chart;
using System.Web.UI.DataVisualization.Charting;
using System.Diagnostics;

namespace MediaMonitor.Users
{
    public partial class Report_Outdoorplanning : Adex.BasePage
    {
        DataTable dtGraph = new DataTable();
        DataColumn column;
        DataRow row;
        DataTable dtExpToExcel = new DataTable();
        DataColumn columnExpToExcel;
        DataRow rowExpToExcel;
        int mediaTypeCode = 0;
        string mediaCodes = "";
        ArrayList arrBilBrdQuantToSend; ArrayList arrBilBrdTypeToSend; ArrayList arrCarrToSend; ArrayList arrSelectedAvenueToSend; ArrayList arrNegotiatedPriceToSend;
        DataTable dtRecords = new DataTable();
       // ArrayList arrExceptionalCaseToSend;
        ArrayList arrExceptionalCaseAvenueNameToSend ;
        ArrayList arrExceptionalCaseAverageVehicleToSend ;
        ArrayList arrExceptionalCaseBillBoardQuantToSend ;
        ArrayList arrExceptionalCaseCarrierToSend  ;
        ArrayList arrExceptionalCaseBillBoardTypeToSend ;
        ArrayList arrExceptionalCaseNegoPriceToSend;

        ArrayList arrTotalVehicles = new ArrayList();
        int i = 0; int days;
        int j = 0;
        DataRow rowRecord;
        DateTime sDate; DateTime eDate;
        protected void Page_Load(object sender, EventArgs e)
        {
            //if (!Page.IsPostBack)
            if (!Request.Form.AllKeys.Contains("ctl00$ctl00$Body$Body$btnExport"))
            {
                lblAedvrtiser.Text = Utility.GetRequestedVal("adv", "");
                lblProduct.Text = Utility.GetRequestedVal("product", "");
                lblSector.Text = Utility.GetRequestedVal("s", "");
                lblSubSector.Text = Utility.GetRequestedVal("ss", "");
                lblCatagory.Text = Utility.GetRequestedVal("cc", "");
                lblFromDate.Text = Utility.GetRequestedVal("sd", "01_01_2000").Replace("_", "/").ToString();
                lblToDate.Text = Utility.GetRequestedVal("ed", "01_01_2000").Replace("_", "/").ToString();
                sDate = Convert.ToDateTime(lblFromDate.Text);
                eDate = Convert.ToDateTime(lblToDate.Text);
                days = (eDate - sDate).Days + 1;
                lblnUMBERoFdAYS.Text = days.ToString();

                arrBilBrdQuantToSend = (ArrayList)Session["SelectedAvenuesBillBoardQuant"];
                arrBilBrdTypeToSend = (ArrayList)Session["SelectedAvenuesBillBoardType"];
                arrCarrToSend = (ArrayList)Session["SelectedAvenuesCarrier"];
                arrNegotiatedPriceToSend = (ArrayList)Session["SelectedNegoPrice"];

                //  arrExceptionalCaseToSend = (ArrayList)Session["ExceptionalCase"];
                arrExceptionalCaseAvenueNameToSend = (ArrayList)Session["ExceptionalCaseAvenueName"];
                arrExceptionalCaseAverageVehicleToSend = (ArrayList)Session["ExceptionalCaseAverageVehicle"];
                arrExceptionalCaseBillBoardQuantToSend = (ArrayList)Session["ExceptionalCaseBillBoardQuantity"];
                arrExceptionalCaseCarrierToSend = (ArrayList)Session["ExceptionalCaseCarrier"];
                arrExceptionalCaseBillBoardTypeToSend = (ArrayList)Session["ExceptionalCaseBillBoardType"];
                arrExceptionalCaseNegoPriceToSend = (ArrayList)Session["ExceptionalCaseNegoPrice"];

                ViewState["arrBilBrdQuantToSend"] = arrBilBrdQuantToSend;
                ViewState["arrBilBrdTypeToSend"] = arrBilBrdTypeToSend;
                ViewState["arrCarrToSend"] = arrCarrToSend;
                ViewState["arrNegotiatedPriceToSend"] = arrNegotiatedPriceToSend;
                ViewState["days"] = days;

                ViewState["arrExceptionalCaseAvenueNameToSend"] = arrExceptionalCaseAvenueNameToSend;
                ViewState["arrExceptionalCaseAverageVehicleToSend"] = arrExceptionalCaseAverageVehicleToSend;
                ViewState["arrExceptionalCaseBillBoardQuantToSend"] = arrExceptionalCaseBillBoardQuantToSend;
                ViewState["arrExceptionalCaseCarrierToSend"] = arrExceptionalCaseCarrierToSend;
                ViewState["arrExceptionalCaseBillBoardTypeToSend"] = arrExceptionalCaseBillBoardTypeToSend;
                ViewState["arrExceptionalCaseNegoPriceToSend"] = arrExceptionalCaseNegoPriceToSend;

                // Create new DataColumn, set DataType, ColumnName and add to DataTable For Graph.    
                column = new DataColumn();
                column.DataType = System.Type.GetType("System.Int32");
                column.ColumnName = "TotalVehicles";
                dtGraph.Columns.Add(column);
                // Create second column.
                column = new DataColumn();
                column.DataType = Type.GetType("System.String");
                column.ColumnName = "SelectedAvenue";
                dtGraph.Columns.Add(column);


                // Create new DataColumn, set DataType, ColumnName and add to DataTable For Export To Excel option. 
                columnExpToExcel = new DataColumn();
                columnExpToExcel.DataType = System.Type.GetType("System.String");
                columnExpToExcel.ColumnName = "Avenue";
                dtExpToExcel.Columns.Add(columnExpToExcel);

                columnExpToExcel = new DataColumn();
                columnExpToExcel.DataType = System.Type.GetType("System.String");
                columnExpToExcel.ColumnName = "Average Vehicle";
                dtExpToExcel.Columns.Add(columnExpToExcel);

                columnExpToExcel = new DataColumn();
                columnExpToExcel.DataType = System.Type.GetType("System.String");
                columnExpToExcel.ColumnName = "Carrier";
                dtExpToExcel.Columns.Add(columnExpToExcel);

                columnExpToExcel = new DataColumn();
                columnExpToExcel.DataType = System.Type.GetType("System.String");
                columnExpToExcel.ColumnName = "Billboards Type";
                dtExpToExcel.Columns.Add(columnExpToExcel);

                columnExpToExcel = new DataColumn();
                columnExpToExcel.DataType = System.Type.GetType("System.String");
                columnExpToExcel.ColumnName = "Negotiated Price ($)";
                dtExpToExcel.Columns.Add(columnExpToExcel);

                columnExpToExcel = new DataColumn();
                columnExpToExcel.DataType = System.Type.GetType("System.String");
                columnExpToExcel.ColumnName = "Billboars Quantity";
                dtExpToExcel.Columns.Add(columnExpToExcel);

                columnExpToExcel = new DataColumn();
                columnExpToExcel.DataType = System.Type.GetType("System.Int32");
                columnExpToExcel.ColumnName = "Total Vehicle";
                dtExpToExcel.Columns.Add(columnExpToExcel);

                columnExpToExcel = new DataColumn();
                columnExpToExcel.DataType = System.Type.GetType("System.String");
                columnExpToExcel.ColumnName = "Daily Cost";
                dtExpToExcel.Columns.Add(columnExpToExcel);

                columnExpToExcel = new DataColumn();
                columnExpToExcel.DataType = System.Type.GetType("System.String");
                columnExpToExcel.ColumnName = "Total Cost";
                dtExpToExcel.Columns.Add(columnExpToExcel);


                //dtRecords = ReportFuncs.GetReportPlanning_Paged(Convert.ToInt32(AppUser.CurrUserID.ToString()), sDate, eDate, Utility.ToInt(Utility.GetRequestedVal("mtc", "0")), Utility.GetRequestedVal("smc", ""), "", 20, 0);

                //if (arrExceptionalCaseAvenueNameToSend != null)
                //{

                //    DataRow theRowToDelete = dtRecords.Rows[dtRecords.Rows.Count - 1];
                //    dtRecords.Rows.Remove(theRowToDelete);
                //    arrExceptionalCaseAvenueNameToSend = (ArrayList)ViewState["arrExceptionalCaseAvenueNameToSend"];
                //    for (int i = 0; i < arrExceptionalCaseAvenueNameToSend.Count; i++)
                //    {
                //        rowRecord = dtRecords.NewRow();
                //        rowRecord["MediaName"] = arrExceptionalCaseAvenueNameToSend[i].ToString() + "AVENIDA NO MONITOREADA POR AGB";
                //        dtRecords.Rows.Add(rowRecord);

                //    }

                //}
                //radGrid.DataSource = dtRecords;
                //radGrid.DataBind();

                // }
                dtRecords = ReportFuncs.GetReportPlanning_Paged(Convert.ToInt32(AppUser.CurrUserID.ToString()), sDate, eDate, Utility.ToInt(Utility.GetRequestedVal("mtc", "0")), Utility.GetRequestedVal("smc", ""), "", 20, 0);

                if (arrExceptionalCaseAvenueNameToSend != null)
                {

                    DataRow theRowToDelete = dtRecords.Rows[dtRecords.Rows.Count - 1];
                    dtRecords.Rows.Remove(theRowToDelete);
                    arrExceptionalCaseAvenueNameToSend = (ArrayList)ViewState["arrExceptionalCaseAvenueNameToSend"];
                    for (int i = 0; i < arrExceptionalCaseAvenueNameToSend.Count; i++)
                    {
                        rowRecord = dtRecords.NewRow();
                        rowRecord["MediaName"] = arrExceptionalCaseAvenueNameToSend[i].ToString() + "AVENIDA NO MONITOREADA POR AGB";
                        dtRecords.Rows.Add(rowRecord);

                    }

                }
                radGrid.DataSource = dtRecords;
                radGrid.DataBind();
            }
        }

        protected void radGrid_NeedDataSource(object source, GridNeedDataSourceEventArgs e)
        {

            //string sDate = Utility.GetRequestedVal("sd", "01_01_2000").Replace("_", "/");
            //string eDate = Utility.GetRequestedVal("ed", "01_01_2000").Replace("_", "/");

            //mediaTypeCode = Utility.ToInt(Utility.GetRequestedVal("mtc", "0"));

            //string searchFromSelectedMediaCodes = Utility.GetRequestedVal("smc", "");

            //objDS.SelectParameters["userID"].DefaultValue = AppUser.CurrUserID.ToString();
            //objDS.SelectParameters["sDate"].DefaultValue = sDate.ToString();
            //objDS.SelectParameters["eDate"].DefaultValue = eDate.ToString();
            //objDS.SelectParameters["mediaTypeCode"].DefaultValue = mediaTypeCode.ToString();
            //objDS.SelectParameters["mediaCodes"].DefaultValue = searchFromSelectedMediaCodes;
            //radGrid.DataSourceID = "objDS";

                            

        }


        protected void ObjDS_Selecting(object sender, ObjectDataSourceSelectingEventArgs e)
        {
            //if (!e.ExecutingSelectCount)
            //{

            //    e.Arguments.MaximumRows = radGrid.PageSize;
            //    e.InputParameters.Add("e", e);
            //}
        }


        protected void ObjDS_SelectingExport(object sender, ObjectDataSourceSelectingEventArgs e)
        {
            if (!e.ExecutingSelectCount)
            {
                e.Arguments.MaximumRows = radGrid.PageSize;
                e.InputParameters.Add("e", e);
            }
        }

        protected void btnExport_Clicked(object sender, EventArgs e)
        {
            switch (Request.Browser.Browser) { 
                case "IE":
                    break;
                case "Firefox":
                 //   ChangeDownloadOption();
                   // Process.Start("Firefox");
                    break;
                case "Chrome":
                    break;
            }
         
            DataTable dtUserSelection = CreateDatatableofUserSelection();
            DataTable dtExpToExcel = (DataTable)ViewState["dtExpToExcel"];
            DumpExcel(dtUserSelection, dtExpToExcel);
        }

        protected void radGrid_ItemDataBound(object sender, GridItemEventArgs e)
        {
            arrSelectedAvenueToSend = (ArrayList)Session["SelectedAvenueToSend"];
            int TotalVehicleUpper = 0; int TotalCostUpper = 0; string resultTotalVeh = string.Empty; string resultTotalCost = string.Empty;
            string NegPrice = string.Empty; string DailyCostFormat = string.Empty; string TotalVehFormat = string.Empty; string TotalCostFormat = string.Empty;
            string AveragVehFormat = string.Empty;
            int BillQuant = 0; int avgVeh = 0; int TotalVeh = 0; int NegoPrice = 0; int DailyCost = 0;
            if (e.Item is GridDataItem)
            {
                GridDataItem item = (GridDataItem)e.Item;
                if (arrBilBrdQuantToSend != null)
                {
                    if (i <= arrBilBrdQuantToSend.Count - 1)
                    {
                        arrBilBrdQuantToSend = (ArrayList)ViewState["arrBilBrdQuantToSend"];
                        ((Label)item["Billboardsquantity"].FindControl("lblBillboardsQuantity")).Text = arrBilBrdQuantToSend[i].ToString();

                        BillQuant = Convert.ToInt32(((Label)item["Billboardsquantity"].FindControl("lblBillboardsQuantity")).Text);
                        if (((Label)item["AverageVehicle"].FindControl("lblAverageVehicleHidden")).Text != "")
                            avgVeh = Convert.ToInt32(((Label)item["AverageVehicle"].FindControl("lblAverageVehicleHidden")).Text);
                        TotalVeh = BillQuant * avgVeh;
                        TotalVehFormat = string.Format("{0:###,###}", TotalVeh);
                        ((Label)item["TotalVehicle"].FindControl("lblTotalVehicle")).Text = TotalVehFormat;
                        ((Label)item["TotalVehicle"].FindControl("lblTotalVehicleHidden")).Text = TotalVeh.ToString();

                        arrNegotiatedPriceToSend = (ArrayList)ViewState["arrNegotiatedPriceToSend"];
                        NegoPrice = Convert.ToInt32(arrNegotiatedPriceToSend[i].ToString());
                        NegPrice = string.Format("{0:###,###}", NegoPrice);
                        ((Label)item["NegotiatedPrice"].FindControl("lblNegotiatedPrice")).Text = NegPrice;

                        DailyCost = NegoPrice / Convert.ToInt32(ViewState["days"]);
                        DailyCostFormat = string.Format("{0:###,###}", DailyCost);
                        ((Label)item["DailyCost"].FindControl("lblDailyCost")).Text = DailyCostFormat;

                        ((Label)item["TotalCost"].FindControl("lblTotalCost")).Text = NegPrice;//Because Negotiated Price and Total Cost are same.

                        arrBilBrdTypeToSend = (ArrayList)ViewState["arrBilBrdTypeToSend"];
                        ((Label)item["BillboardsType"].FindControl("lblBillboardsTYpe")).Text = arrBilBrdTypeToSend[i].ToString();

                        arrCarrToSend = (ArrayList)ViewState["arrCarrToSend"];
                        ((Label)item["Carrier"].FindControl("lblCarrier")).Text = arrCarrToSend[i].ToString();

                        i++;

                    }
                }
                //This is to handel Exceptional case.
              
                if (((Label)item["Media"].FindControl("lblMedia")).Text.Contains("AVENIDA NO MONITOREADA POR AGB"))
                {
                   
                    arrExceptionalCaseAvenueNameToSend = (ArrayList)ViewState["arrExceptionalCaseAvenueNameToSend"];
                    ((Label)item["Media"].FindControl("lblMediaHidden")).Visible = true;
                    ((Label)item["Media"].FindControl("lblMediaHidden")).Text ="**";
                    ((Label)item["Media"].FindControl("lblMedia")).Text = arrExceptionalCaseAvenueNameToSend[j].ToString();
                    lblExceptionalCase.Visible = true;
                    spnStars.Visible = true;

                    arrExceptionalCaseCarrierToSend = (ArrayList)ViewState["arrExceptionalCaseCarrierToSend"];
                    ((Label)item["Carrier"].FindControl("lblCarrier")).Text = arrExceptionalCaseCarrierToSend[j].ToString();

                    arrExceptionalCaseBillBoardTypeToSend = (ArrayList)ViewState["arrExceptionalCaseBillBoardTypeToSend"];
                    ((Label)item["BillboardsType"].FindControl("lblBillboardsTYpe")).Text = arrExceptionalCaseBillBoardTypeToSend[j].ToString();

                    arrExceptionalCaseBillBoardQuantToSend = (ArrayList)ViewState["arrExceptionalCaseBillBoardQuantToSend"];
                    ((Label)item["Billboardsquantity"].FindControl("lblBillboardsQuantity")).Text = arrExceptionalCaseBillBoardQuantToSend[j].ToString();

                    arrExceptionalCaseAverageVehicleToSend = (ArrayList)ViewState["arrExceptionalCaseAverageVehicleToSend"];
                    int Avg = Convert.ToInt32(arrExceptionalCaseAverageVehicleToSend[j]);
                    AveragVehFormat = string.Format("{0:###,###}", Avg);
                    ((Label)item["AverageVehicle"].FindControl("lblAverageVehicle")).Text = AveragVehFormat;
                    ((Label)item["AverageVehicle"].FindControl("lblAverageVehicleHidden")).Text = arrExceptionalCaseAverageVehicleToSend[j].ToString();

                    arrExceptionalCaseBillBoardQuantToSend = (ArrayList)ViewState["arrExceptionalCaseBillBoardQuantToSend"];
                    avgVeh = Convert.ToInt32(((Label)item["AverageVehicle"].FindControl("lblAverageVehicleHidden")).Text);
                    TotalVeh = Convert.ToInt32(arrExceptionalCaseBillBoardQuantToSend[j]) * avgVeh;
                    TotalVehFormat = string.Format("{0:###,###}", TotalVeh);
                    ((Label)item["TotalVehicle"].FindControl("lblTotalVehicle")).Text = TotalVehFormat;
                    ((Label)item["TotalVehicle"].FindControl("lblTotalVehicleHidden")).Text = TotalVeh.ToString();

                    arrExceptionalCaseNegoPriceToSend = (ArrayList)ViewState["arrExceptionalCaseNegoPriceToSend"];
                    NegoPrice = Convert.ToInt32(arrExceptionalCaseNegoPriceToSend[j].ToString());
                    DailyCost = NegoPrice / Convert.ToInt32(ViewState["days"]);
                    DailyCostFormat = string.Format("{0:###,###}", DailyCost);
                    ((Label)item["DailyCost"].FindControl("lblDailyCost")).Text = DailyCostFormat;

                    arrExceptionalCaseNegoPriceToSend = (ArrayList)ViewState["arrExceptionalCaseNegoPriceToSend"];
                    NegoPrice = Convert.ToInt32(arrExceptionalCaseNegoPriceToSend[j].ToString());
                    NegPrice = string.Format("{0:###,###}", NegoPrice);
                    ((Label)item["TotalCost"].FindControl("lblTotalCost")).Text = NegPrice;
                    ((Label)item["NegotiatedPrice"].FindControl("lblNegotiatedPrice")).Text = NegPrice;
                    j++;
                }

                //Tis is to show total Vehical in Summamry.
                ViewState["TVeh"] = TotalVeh;
                TotalVehicleUpper = Convert.ToInt32(ViewState["TVehUpper"]) + Convert.ToInt32(ViewState["TVeh"]);
                ViewState["TVehUpper"] = TotalVehicleUpper;
                resultTotalVeh = string.Format("{0:###,###}", TotalVehicleUpper);
                lblTotalVehicle.Text = resultTotalVeh;

                //Tis is to show total Cost in Summamry.
                ViewState["TCost"] = NegoPrice;
                TotalCostUpper = Convert.ToInt32(ViewState["TCostUpper"]) + Convert.ToInt32(ViewState["TCost"]);
                ViewState["TCostUpper"] = TotalCostUpper;
                resultTotalCost = string.Format("{0:###,###}", TotalCostUpper);
                lblTotalCost.Text = resultTotalCost;

                //This is for graph.
                row = dtGraph.NewRow();
                row["TotalVehicles"] = TotalVeh;
                row["SelectedAvenue"] = ((Label)item["Media"].FindControl("lblMedia")).Text;
             
                dtGraph.Rows.Add(row);
                ChartAvenue.ChartAreas["ChartArea1"].AxisX.Interval = 1;
                ChartAvenue.ChartAreas["ChartArea1"].AxisX.IsLabelAutoFit = true;
                ChartAvenue.ChartAreas["ChartArea1"].AxisX.LabelAutoFitStyle = LabelAutoFitStyles.LabelsAngleStep45;
                ChartAvenue.ChartAreas[0].AxisX.LabelStyle.Angle = -40;
                ChartAvenue.Series[0].SmartLabelStyle.Enabled = false;
                ChartAvenue.DataSource = dtGraph;
                ChartAvenue.DataBind();
               

                //This is for Export To Excel Option.
                rowExpToExcel = dtExpToExcel.NewRow();
                string tocmp = ((Label)item["Media"].FindControl("lblMediaHidden")).Text;
                if (tocmp.Contains("**"))
                    rowExpToExcel["Avenue"] = "**" + ((Label)item["Media"].FindControl("lblMedia")).Text;
                else
                    rowExpToExcel["Avenue"] = ((Label)item["Media"].FindControl("lblMedia")).Text;
                rowExpToExcel["Average Vehicle"] = ((Label)item["AverageVehicle"].FindControl("lblAverageVehicle")).Text;
                rowExpToExcel["Carrier"] = ((Label)item["Carrier"].FindControl("lblCarrier")).Text;
                rowExpToExcel["Billboards Type"] = ((Label)item["BillboardsType"].FindControl("lblBillboardsTYpe")).Text;
                rowExpToExcel["Negotiated Price ($)"] = ((Label)item["NegotiatedPrice"].FindControl("lblNegotiatedPrice")).Text;
                rowExpToExcel["Billboars Quantity"] = ((Label)item["Billboardsquantity"].FindControl("lblBillboardsQuantity")).Text;
                if (((Label)item["TotalVehicle"].FindControl("lblTotalVehicleHidden")).Text != "")
                    rowExpToExcel["Total Vehicle"] = ((Label)item["TotalVehicle"].FindControl("lblTotalVehicleHidden")).Text;
                rowExpToExcel["Daily Cost"] = ((Label)item["DailyCost"].FindControl("lblDailyCost")).Text;
                rowExpToExcel["Total Cost"] = ((Label)item["TotalCost"].FindControl("lblTotalCost")).Text;
                dtExpToExcel.Rows.Add(rowExpToExcel);

                ViewState["dtExpToExcel"] = dtExpToExcel;
            }

        }



        private DataTable CreateDatatableofUserSelection()
        {

            DataTable tableUserSelection = new DataTable();
            tableUserSelection.Columns.Add("Fields", typeof(string));
            tableUserSelection.Columns.Add("User Selection", typeof(string));

            tableUserSelection.Rows.Add("Advertiser", lblAedvrtiser.Text);
            tableUserSelection.Rows.Add("Product", lblProduct.Text);
            tableUserSelection.Rows.Add("Sector Name", lblSector.Text);
            tableUserSelection.Rows.Add("SubSector Name", lblSubSector.Text);
            tableUserSelection.Rows.Add("Category", lblCatagory.Text);
            tableUserSelection.Rows.Add("Total Cost", lblTotalCost.Text);
            tableUserSelection.Rows.Add("Total Vehicle", lblTotalVehicle.Text);
            tableUserSelection.Rows.Add("From Date", lblFromDate.Text);
            tableUserSelection.Rows.Add("To Date", lblToDate.Text);
            tableUserSelection.Rows.Add("No. Of Days", lblnUMBERoFdAYS.Text);
           
            return tableUserSelection;

        }


        private void DumpExcel(DataTable tblUserSelection, DataTable dtExpToExcel)
        {
            Int32 tblRowCount = dtExpToExcel.Rows.Count;

            if (tblRowCount > 0)
            {
                using (ExcelPackage pck = new ExcelPackage())
                {
                    try
                    {
                        //Create the worksheet
                        ExcelWorksheet ws = pck.Workbook.Worksheets.Add("MediaMonitor-PlanningReport");
                        ws.Cells["A1"].LoadFromDataTable(tblUserSelection, true);
                        ws.DefaultColWidth = 20;
                        ws.Column(7).Style.Numberformat.Format = "###,###";
                      

                        using (ExcelRange rng = ws.Cells["A1:B1"])
                        {
                            rng.Style.Font.Bold = true;
                        }
                        using (ExcelRange rng = ws.Cells["A13:I13"])
                        {
                            rng.Style.Font.Bold = true;
                        }
                        ws.Cells["A13:I13"].AutoFilter = true;
                        ws.Cells["A13"].LoadFromDataTable(dtExpToExcel, true);
                        //ws.Cells["A" + (tblRowCount + 13).ToString()].Style.WrapText = true;
                        string LastCellData = ws.Cells["A" + (tblRowCount + 13).ToString()].Text;
                        if (LastCellData.Contains("**"))
                            ws.Cells["A" + (tblRowCount + 14).ToString()].RichText.Add("** AVENIDA NO MONITOREADA POR AGB").Bold = true;

                        var BarChart = ws.Drawings.AddChart("Vehicle Chart", eChartType.ColumnClustered) as ExcelBarChart;
                        BarChart.SetPosition(tblRowCount + 15, 2, 2, 2);
                        BarChart.SetSize(800, 400);
                        BarChart.Title.Text = "TOTAL VEHICLE";
                        BarChart.Style = eChartStyle.Style26;
                        BarChart.Legend.Remove();
                        BarChart.DataLabel.ShowValue = true;
                        BarChart.DataLabel.Font.Bold = true;
                        BarChart.DataLabel.Border.LineCap = OfficeOpenXml.Drawing.eLineCap.Round;
                        BarChart.YAxis.Format = "###,###";
                        BarChart.Series.Add(ExcelRange.GetAddress(14, 7, tblRowCount + 13, 7), ExcelRange.GetAddress(14, 1, tblRowCount + 13, 1));
                    }
                    catch { }
                    Response.Clear();
                    Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
                    Response.AddHeader("content-disposition", "attachment;  filename=MediaMonitor-PlanningReport.xlsx");
                    Response.BinaryWrite(pck.GetAsByteArray());
                    Response.End();
                }
            }
        }

        private void InngridExport()
        {
            string sDate = Utility.GetRequestedVal("sd", "01_01_2000").Replace("_", "/");
            string eDate = Utility.GetRequestedVal("ed", "01_01_2000").Replace("_", "/");

            mediaTypeCode = Utility.ToInt(Utility.GetRequestedVal("mtc", "0"));

            bool searchFromAllMedia = Utility.ToBool(Utility.GetRequestedVal("mc", "false"));

            try
            {
                mediaCodes = searchFromAllMedia == false ? Session["SearchMediaCodes_outdoor"].ToString() : "";
            }
            catch
            {
                mediaCodes = "";
            }

            objDSExport.SelectParameters["userID"].DefaultValue = AppUser.CurrUserID.ToString();
            objDSExport.SelectParameters["sDate"].DefaultValue = sDate.ToString();
            objDSExport.SelectParameters["eDate"].DefaultValue = eDate.ToString();
            objDSExport.SelectParameters["mediaTypeCode"].DefaultValue = mediaTypeCode.ToString();
            objDSExport.SelectParameters["mediaCodes"].DefaultValue = mediaCodes;
        }

        #region Change Browers's Download Option
        public void ChangeDownloadOption()
        {
            //if (isFireFoxOpen())
            //{
            //    Console.WriteLine("Firefox is open, close it");
            //}
            //else
            //{
                string pathOfPrefsFile = GetPathOfPrefsFile();
                updateSettingsFile(pathOfPrefsFile);
            //}
        }
        public static bool isFireFoxOpen()
        {
            foreach (Process proc in Process.GetProcesses())
            {
                if (proc.ProcessName == "firefox")
                {
                    return true;
                }
            }
            return false;
        }
        private static string GetPathOfPrefsFile()
        {
            // Get roaming folder, and get the profiles.ini
            string iniFilePath = Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData) + @"\Mozilla\Firefox\profiles.ini";
            // Profiles.ini tells us what folder the preferences file is in.
            string contentsOfIni = File.ReadAllText(iniFilePath);

            int locOfPath = contentsOfIni.IndexOf("Path=Profiles");
            int endOfPath = contentsOfIni.IndexOf(".default", locOfPath);

            int startOfPath = locOfPath + "Path=Profiles".Length + 1;
            int countofCopy = ((endOfPath + ".default".Length) - startOfPath);
            string path = contentsOfIni.Substring(startOfPath, countofCopy);

            string toReturn = Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData) + @"\Mozilla\Firefox\Profiles\" + path + @"\prefs.js";
            return toReturn;
        }
        private static void updateSettingsFile(string pathOfPrefsFile)
        {
            string[] contentsOfFile = File.ReadAllLines(pathOfPrefsFile);
            List<String> outputLines = new List<string>();
            foreach (string line in contentsOfFile)
            {
                outputLines.Add(line);
            }

            // Finally add the value we want to the end
            outputLines.Add("user_pref(\"browser.download.useDownloadDir\", false);");
            // Rename the old file preferences for safety...
            File.Move(pathOfPrefsFile, Path.GetDirectoryName(pathOfPrefsFile) + @"\" + Path.GetFileName(pathOfPrefsFile) + ".OLD." + Guid.NewGuid().ToString());
            // Write the new file.
            File.WriteAllLines(pathOfPrefsFile, outputLines.ToArray());
        }
        #endregion
    }
}